"""
# pip install pymysql
# pip install pymssql
# pip install DBUtils==1.3  --- 特别要注意
# pip install -i https://pypi.doubanio.com/simple paho-mqtt
"""
import json
import time
import pymysql
import pymssql
from DBUtils.PooledDB import PooledDB
import os
import requests
from paho.mqtt import client as mqtt_client
import serial, serial.tools.list_ports
'''
Error Code:
1001 : db code is error.
1002 : db type is error.
1003 : pool params error.
1004 : query faild.
'''


class TPyDB():
    def __init__(self):
        self.settings = {}
        self.pools = {}

    def setSettings(self, code, dbType, host, port, db, uid, pwd, sqlPath):
        try:
            item = {}
            if code in self.settings.keys():
                item = self.settings[code]
            else:
                self.settings[code] = item

            item['code'] = code
            item['dbType'] = dbType
            item['host'] = host
            item['port'] = port
            item['db'] = db
            item['uid'] = uid
            item['pwd'] = pwd
            item['sqlPath'] = sqlPath
        except Exception as er:
            print(er)

    def getPool(self, code):
        pool = None
        status = 0
        try:
            if code in self.pools.keys():
                pool = self.pools[code]
                status = 1
            elif code in self.settings.keys():
                settingItem = self.settings[code]
                if settingItem['dbType'] == 'mysql':
                    pool, status = self.getMysqlPool(settingItem)
                elif settingItem['dbType'] == 'mssql':
                    pool, status = self.getMssqlPool(settingItem)
                else:
                    status = 1002
            else:
                status = 1001
                print("pool Code is error.", code)
        except Exception as er:
            print(er)
        return pool, status

    def getMysqlPool(self, settingItem):
        pool = None
        status = 0
        try:
            pool = PooledDB(
                creator=pymysql,
                mincached=1,
                maxcached=100,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit=True,
                client_flag= pymysql.CLIENT.MULTI_STATEMENTS
            )
            self.pools[settingItem['code']] = pool
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return pool, status

    def getMssqlPool(self, settingItem):
        pool = None
        status = 0
        try:
            pool = PooledDB(
                creator=pymssql,
                maxconnections=200,
                mincached=10,
                maxcached=15,
                maxshared=0,
                maxusage=1000,
                ping=1,
                blocking=True,
                host=settingItem["host"],
                port=settingItem["port"],
                database=settingItem["db"],
                user=settingItem["uid"],
                password=settingItem["pwd"],
                autocommit=True,
                # closeable=False,
            )
            code = settingItem['code']
            self.pools[code] = pool
            status = 1
        except Exception as er:
            status = 1003
            print(er)
        return pool, status

    def getConnection(self, code):
        conn = None
        status = 0
        try:
            pool, poolStatus = self.getPool(code)
            if poolStatus == 1:
                conn = pool.connection()
                status = 1
            else:
                status = poolStatus
        except Exception as er:
            print(er)
        return conn, status

    def query(self, code, sql, params):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            conn, connstatus = self.getConnection(code)
            if connstatus == 1:
                settingItem = self.settings[code]
                dbType = settingItem['dbType']
                cursor = None
                if dbType == 'mysql':
                    cursor = conn.cursor()
                else:
                    cursor = conn.cursor(as_dict=True)
                try:
                    if params != None:
                        cursor.execute(sql, params)
                    else:
                        cursor.execute(sql)
                    if cursor.description != None:
                        rsList = []
                        rs = cursor.fetchall()
                        rsList.append(list(rs))
                        # queryRes['datasetList'].append(list(rs))
                        while cursor.nextset():
                            rs = cursor.fetchall()
                            rsList.append(list(rs))
                            # queryRes['datasetList'].append(list(rs))
                        queryRes['datasetList'] = rsList
                        if len(queryRes['datasetList']) > 0:
                            queryRes['recordset'] = queryRes['datasetList'][0]
                    else:
                        queryRes['executeResult'] = {
                            "rowNum": cursor.rownumber,
                            "rowCount": cursor.rowcount,
                            "newId": cursor.lastrowid
                        }
                    queryRes['queryStatus'] = 1
                except Exception as eer:
                    print('query faild.', eer)
                    queryRes['queryStatus'] = 1004
                    queryRes['errMsg'] = eer;
                cursor.close()
                cursor = None
            else:
                queryRes['queryStatus'] = connstatus
        except Exception as er:
            print(er)
        return queryRes

    def queryByKey(self, code, key, params):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                fn = os.path.join(self.settings[code]["sqlPath"], key)
                _, ext = os.path.splitext(fn)
                if ext == '':
                    fn = fn + '.txt'
                if os.path.exists(fn):
                    sql = ""
                    with open(fn, "r", encoding='utf-8') as f:
                        sql = f.read()
                    queryRes = self.query(code, sql, params)
                else:
                    print('file not exists :', fn)
                    queryRes['queryStatus'] = 1005
                    queryRes['errMsg'] = "key(" + key + ") is not exists."
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes

    def createRow(self, code, table, fieldValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                fields = ','.join([x for x in fieldValues.keys()])
                values = ",".join(["%(" + x + ")s" for x in fieldValues.keys()])
                sql = "insert into " + table + "(" + fields + ") values (" + values + ")"
                queryRes = self.query(code, sql, fieldValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes

    def updateRow(self, code, table, fieldValues, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                fields = ','.join([x + '=%(' + x + ')s' for x in fieldValues.keys()])
                keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
                sql = "update " + table + ' set ' + fields + ' where ' + keys
                ps = {}
                for k in fieldValues.keys():
                    ps[k] = fieldValues[k]
                for k in keyValues.keys():
                    ps[k] = keyValues[k]
                queryRes = self.query(code, sql, ps)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes

    def deleteRow(self, code, table, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
                sql = "delete from  " + table + ' where ' + keys
                queryRes = self.query(code, sql, keyValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes

    def viewRows(self, code, view, keyValues):
        queryRes = {
            "queryStatus": 0,
            "errMsg": "",
            "executeResult": {},
            "datasetList": [],
            "recordset": []
        }
        try:
            if code in self.settings.keys():
                keys = " and ".join([x + '=%(' + x + ')s' for x in keyValues.keys()])
                sql = "select * from  " + view + ' where ' + keys
                queryRes = self.query(code, sql, keyValues)
            else:
                queryRes['queryStatus'] = 1001
        except Exception as er:
            print(er)
        return queryRes


class TPyInfluxDB():
    def __init__(self):
        self.settings = {}
        '''
        self.host = ""
        self.port=0
        self.token=""
        self.db = ""
        '''

    def setSettings(self, code="", host="", port=0, db="", token="", sqlPath=""):
        try:
            item = {}
            if code in self.settings.keys():
                item = self.settings[code]
            else:
                self.settings[code] = item

            item['code'] = code
            item['host'] = host
            item['port'] = port
            item['token'] = token
            item['db'] = db
            item['sqlPath'] = sqlPath
        except Exception as er:
            print(er)

    def write2TDB(self, tdbCode, datapointStr, rp=""):
        res = 0
        try:
            if tdbCode in self.settings.keys():
                settings = self.settings[tdbCode]
                host = settings['host']
                port = settings['port']
                token = settings['token']
                db = settings['db']
                headers = {
                    "Authorization": token,
                    'Content-Type': 'application/json',
                }
                influxdb_url = "http://" + host + ":" + str(port) + '/write?db=' + db
                if rp != '':
                    influxdb_url = influxdb_url + '&rp=rePolicy_1h'
                resp = requests.post(url=influxdb_url, headers=headers, data=datapointStr)

                if str(resp.status_code)=='204' or str(resp.status_code)=='200':
                    res = 1
                # print(resp)
            else:
                res = 1001
            # print('save to tdb:', resp)
        except Exception as er:
            print(er)
        return res
    def query(self , tdbCode , sql , rp=''):
        res = {
            "status":0 ,
            "result":None
        }
        try:
            if tdbCode in self.settings.keys():
                settings = self.settings[tdbCode]
                host = settings['host']
                port = settings['port']
                token = settings['token']
                db = settings['db']
                headers = {
                    "Authorization": token,
                    'Content-Type': 'application/json',
                }
                influxdb_url = "http://" + host + ":" + str(port) + '/query?db=' + db
                if rp != '':
                    influxdb_url = influxdb_url + '&rp=rePolicy_1h'
                influxdb_url = influxdb_url +"&q="+sql
                print(sql)
                resp = requests.get(url=influxdb_url , headers= headers  )
                if resp.status_code == 200 :
                    respData = json.loads( resp.content )
                    res['result'] = respData['results']
                    res['status'] = 1

            else:
                res['status'] = 1001
        except Exception as er :
            print(er)
        return res

    def queryByFn(self , tdbCode , dbKey , queryPs =None):
        res = {
            "status":0 ,
            "result":None
        }
        try:
            if tdbCode in self.settings.keys():
                settings = self.settings[tdbCode]
                sqlPath = os.path.abspath(settings['sqlPath'])
                sqlFn = dbKey if dbKey.find('.txt') >0 else  dbKey+".txt"
                fn = os.path.join(sqlPath , sqlFn)
                if os.path.exists(fn):
                    content = ""
                    with open(fn , 'r' , encoding='utf-8') as f :
                        content = f.read()
                    sql = content
                    if queryPs!=None:
                        sql = content.format(**queryPs)
                    res = self.query( tdbCode , sql)
                else:
                    res["status"] = 1002
                    print(' tdb dbKey is ['+fn+'] not exists')
            else:
                res['status'] = 1001
        except Exception as er :
            print(er)
        return res

class TMq:
    def __init__(self):
        self.host = "127.0.0.1"
        self.port = 31274
        self.uid = "admin"
        self.pwd = "hhuc1115"
        self.clientId = ""
        self.client = None
        self.subscribeTopics = []
        self.rootTopic = ""
        self.actived = False
        self.events = {
            "onConnect": self.eventOnConnect,  # eventOnConnect(client, userdata, flags, rc)
            "onDisConnect": self.eventOnDisconnect,  # eventOnDisConnect(client, userdata, rc)
            "onMessage": self.eventOnMessage,  # eventOnMessage(client, userdata, topic, payload,  message)
        }

    def setSettings(self, host="", port=0, uid="", pwd="", rootTopic="", subscribeTopics=[]):
        try:
            self.host = host
            self.port = port
            self.uid = uid
            self.pwd = pwd
            self.rootTopic =rootTopic
            if len(subscribeTopics) > 0:
                self.subscribeTopics = subscribeTopics
        except Exception as er:
            res = False
            print(er)

    def isConnected(self):
        res = False
        try:
            res = self.client != None and self.actived
        except Exception as er:
            res = False
            print(er)
        return res

    def connect(self):
        try:
            userData = {"o:""mqc-2024"}
            clientId = "mqc" + str(time.time())
            if self.client == None:
                self.client = mqtt_client.Client(client_id=clientId, clean_session=True, userdata=userData,
                                                 transport="tcp")
            else:
                self.client.reinitialise(clientId, clean_session=True, userdata=userData)
            self.client.username_pw_set(username=self.uid, password=self.pwd)
            self.client.on_connect = self.on_connect
            self.client.on_disconnect = self.on_disconnect
            self.client.on_message = self.on_message
            self.client.connect(host=self.host, port=self.port, keepalive=60)
            self.actived = True
        except Exception as er:
            print(er)
        res = self.isConnected()
        return res

    def start(self, asyncFlag=False):
        try:
            self.connect()
            time.sleep(1.0)
            flag = self.isConnected()
            if flag:
                if asyncFlag:
                    self.client.loop_start()
                else:
                    self.client.loop_forever()
        except Exception as er:
            print(er)
        res = self.isConnected()
        return res

    def stop(self):
        try:
            if self.isConnected():
                self.client.loop_stop()
                self.client.disconnect()
            if self.client!=None:
                self.client = None
        except Exception as er:
            print(er)

    def restart(self, asyncFlag=False):
        try:
            self.stop()
            self.start(asyncFlag)
        except Exception as er:
            print(er)

    def sendMsg(self, topic, messagePayload):
        try:
            flag = self.isConnected()
            if flag:
                self.client.publish(topic=topic, payload=messagePayload, retain=False)
        except Exception as er:
            print(er)

    def subscribe(self, topics):
        try:
            self.client.subscribe(topics)
        except Exception as er:
            print(er)

    def on_connect(self, client, userdata, flags, rc):
        try:
            print('connect to mqtt broker', "flags=", flags, "rc=", rc)
            if rc == 0:
                self.actived = True
                if len(self.subscribeTopics) > 0:
                    for topic in self.subscribeTopics:
                        self.client.subscribe(topic)
            else:
                self.actived = False
            if "onConnect" in self.events.keys() and self.events["onConnect"] != None:
                fun = self.events["onConnect"]
                fun(client, userdata, flags, rc)
        except Exception as er:
            print(er)

    def on_disconnect(self, client, userdata, rc):
        try:
            print('disconnect from mqtt broker', "rc=", rc)
            self.actived = False
            if "onDisConnect" in self.events.keys() and self.events["onDisConnect"] != None:
                fun = self.events["onDisConnect"]
                fun(client, userdata, rc)
        except Exception as er:
            print(er)

    def on_message(self, client, userdata, message):
        try:
            print('rec message from  mqtt broker', message)
            print('topic=', message.topic)
            print('payload=', str(message.payload))
            if "onMessage" in self.events.keys() and self.events["onMessage"] != None:
                fun = self.events["onMessage"]
                fun(client, userdata, message.topic, str(message.payload), message)
        except Exception as er:
            print(er)

    def eventOnConnect(self, client, userdata, flags, rc):
        try:
            pass
        except Exception as er:
            print(er)

    def eventOnDisconnect(self, client, userdata, rc):
        try:
            pass
        except Exception as er:
            print(er)

    def eventOnMessage(self, client, userdata, topic, payload, message):
        try:
            pass
        except Exception as er:
            print(er)

class TZn645:
    def __init__(self):
        self.comPort = 'COM8'
        self.comBaudrate = 1200
        self.comParity = 'E'  # serial.PARITY_EVEN ,
        self.comTimeout = 2
        self.serial = serial.Serial()

    def isOpen(self):
        res = False
        try:
            if self.serial != None:
                res = self.serial.isOpen()
        except Exception as er:
            print(er)
        return res

    def open(self):
        try:
            if not self.isOpen():
                self.serial = serial.Serial(
                    self.comPort, self.comBaudrate, parity=self.comParity, timeout=self.comTimeout
                )
        except Exception as er:
            print(er)
        return self.isOpen()

    def close(self):
        try:
            if self.serial != None:
                if self.serial.isOpen():
                    self.serial.close()
                self.serial = None
        except Exception as er:
            print(er)

    def readValue(self, address, dataFlag):
        status = 0
        res = ''
        try:
            sendBytes = self.getSendBytes(address, dataFlag)
            self.serial.write(sendBytes)
            resBytes = self.serial.readline()
            resBytes = resBytes[resBytes.index(0x68):]

            addrBytes = ["{:0>2d}".format((x // 16) * 10 + x % 16) for x in resBytes[1:7]]
            addrBytes.reverse()
            addr = ''.join(addrBytes)

            dataBytes = ["{:0>2d}".format(int(hex(x - 0x33).replace('0x', ''))) for x in resBytes[14:-2]]
            dataBytes.reverse()
            res = "".join(dataBytes)

            status = 1
        except Exception as er:
            print(er)
        return status, res

    def getSendBytes(self, address, dataFlag):
        res = []
        try:
            res.append(0x68)
            for i in range(6):
                pos = (6 - (i + 1)) * 2
                addr = int(address[pos:pos + 2])
                addr = (addr // 10) * 16 + addr % 10
                res.append(addr)
            res.append(0x68)
            res.append(0x11)
            res.append(0x04)
            for i in range(4):
                di = int(dataFlag[i]) + 0x33
                res.append(di)
            c = 0
            for i in res:
                c = c + i
                c = c % 256
            res.append(c)
            res.append(0x16)
        except Exception as er:
            print(er)
        return bytearray(res)




